IF OBJECT_ID('ssf.dpnd_transform_object_header_as_text') IS NOT NULL
	DROP FUNCTION ssf.dpnd_transform_object_header_as_text
GO

CREATE FUNCTION ssf.dpnd_transform_object_header_as_text (
	@object_header XML (ssf.object_header)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Given an object header as XML, parse and format it as text.
(Would be cool to use XSLT for this instead.)
	]]></purpose>
</object_summary>

</object_header>
*/

	-- ============
	/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
	DECLARE	@object_header_text NVARCHAR(MAX) = N''
				, @crlf CHAR(2) = CHAR(13) + CHAR(10)
				, @tab CHAR(1) = CHAR(9)
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare summary variables]]></code_comment>*/
	DECLARE	@s_object_name NVARCHAR(256)
				, @s_object_type NVARCHAR(128)
				, @s_purpose NVARCHAR(MAX)
				, @s_return_data_type NVARCHAR(128)
	-- ============

	-- ============
	/*<code_comment><![CDATA[capture object summary]]></code_comment>*/
	SELECT @s_object_name = @object_header.value('(/object_header/object_summary/dpnd_object_name)[1]', 'NVARCHAR(256)')
			, @s_object_type = @object_header.value('(/object_header/object_summary/object_type)[1]', 'NVARCHAR(128)')
			, @s_purpose = @object_header.value('(/object_header/object_summary/purpose)[1]', 'NVARCHAR(MAX)')
			, @s_return_data_type = @object_header.value('(/object_header/object_summary/return_data_type)[1]', 'NVARCHAR(128)')

	SET @object_header_text = @object_header_text + 'OBJECT SUMMARY' + @crlf + @crlf
	SET @object_header_text = @object_header_text + 'Object Name: ' + @s_object_name
	SET @object_header_text = @object_header_text + @crlf + 'Object Type: ' + @s_object_type
	SET @object_header_text = @object_header_text + @crlf + 'Return Type: ' + @s_return_data_type
	IF @s_purpose IS NOT NULL
		SET @object_header_text = @object_header_text + @crlf + 'Purpose: ' + @s_purpose
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare parameter variables]]></code_comment>*/
	DECLARE	@p_cursor CURSOR
				, @p_order SMALLINT
				, @p_name NVARCHAR(128)
				, @p_desc NVARCHAR(2048)
				, @p_data_type NVARCHAR(128)
				, @p_default_value NVARCHAR(512)
				, @p_return_type VARCHAR(16)
	-- ============

	-- ============
	/*<code_comment><![CDATA[begin cursor: for each parameter]]></code_comment>*/
	SET @p_cursor = CURSOR FOR
	SELECT parameter_dependency.value('(/parameter_dependency/parameter_order)[1]', 'SMALLINT')
		, parameter_dependency.value('(/parameter_dependency/parameter_name)[1]', 'NVARCHAR(128)')
		, parameter_dependency.value('(/parameter_dependency/parameter_desc)[1]', 'NVARCHAR(2048)')
		, parameter_dependency.value('(/parameter_dependency/data_type)[1]', 'NVARCHAR(128)')
		, parameter_dependency.value('(/parameter_dependency/default_value)[1]', 'NVARCHAR(512)')
		, parameter_dependency.value('(/parameter_dependency/return_type)[1]', 'VARCHAR(16)')
	FROM (
		SELECT t.c.query('.') AS parameter_dependency FROM @object_header.nodes('/object_header/parameter_dependency') AS t(c)
	) x
	ORDER BY parameter_dependency.value('(/parameter_dependency/parameter_order)[1]', 'SMALLINT')

	OPEN @p_cursor

	FETCH NEXT FROM @p_cursor INTO @p_order, @p_name, @p_desc, @p_data_type, @p_default_value, @p_return_type

	IF @@FETCH_STATUS <> -1
		SET @object_header_text = @object_header_text + @crlf + @crlf + 'OBJECT PARAMETERS'
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		-- ============
		/*<code_comment><![CDATA[capture parameter dependency]]></code_comment>*/
		SET @object_header_text = @object_header_text + @crlf + @crlf + N'Parameter' + ISNULL(CAST(@p_order AS NVARCHAR), N'') + N': ' + @p_name
		SET @object_header_text = @object_header_text + @crlf + N'Data Type: ' + @p_data_type
		IF @p_default_value IS NOT NULL
			SET @object_header_text = @object_header_text + @crlf + N'Default Value: ' + @p_default_value
		SET @object_header_text = @object_header_text + @crlf + N'Parameter Type: ' + @p_return_type
		IF @p_desc IS NOT NULL
			SET @object_header_text = @object_header_text + @crlf + N'Parameter Description: ' + @p_desc
		-- ============
		FETCH NEXT FROM @p_cursor INTO @p_order, @p_name, @p_desc, @p_data_type, @p_default_value, @p_return_type
	END
	/*<code_comment><![CDATA[end cursor: for each parameter]]></code_comment>*/
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare input dependency variables]]></code_comment>*/
	DECLARE	@i_cursor CURSOR
				, @i_type NVARCHAR(128)
				, @i_name NVARCHAR(512)
	-- ============
	-- ============
	/*<code_comment><![CDATA[begin cursor: for each input dependency]]></code_comment>*/
	SET @i_cursor = CURSOR FOR
	SELECT input_dependency.value('(/input_dependency/object_type)[1]', 'NVARCHAR(128)')
		, input_dependency.value('(/input_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)')
	FROM (
		SELECT t.c.query('.') AS input_dependency FROM @object_header.nodes('/object_header/input_dependency') AS t(c)
	) x

	OPEN @i_cursor

	FETCH NEXT FROM @i_cursor INTO @i_type, @i_name

	IF @@FETCH_STATUS <> -1
		SET @object_header_text = @object_header_text + @crlf + @crlf + 'INPUT DEPENDENCIES - ' + @s_object_name + ' depends on the following objects:' + @crlf
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		-- ============
		/*<code_comment><![CDATA[capture input dependency]]></code_comment>*/
		SET @object_header_text = @object_header_text + @crlf + @tab + @i_type + ' : ' + @i_name
		-- ============
		FETCH NEXT FROM @i_cursor INTO @i_type, @i_name
	END
	/*<code_comment><![CDATA[end cursor: for each input dependency]]></code_comment>*/
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare temporary dependency variables]]></code_comment>*/
	DECLARE	@t_cursor CURSOR
				, @t_type NVARCHAR(128)
				, @t_name NVARCHAR(512)
	-- ============
	-- ============
	/*<code_comment><![CDATA[begin cursor: for each temporary dependency]]></code_comment>*/
	SET @t_cursor = CURSOR FOR
	SELECT temporary_dependency.value('(/temporary_dependency/object_type)[1]', 'NVARCHAR(128)')
		, temporary_dependency.value('(/temporary_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)')
	FROM (
		SELECT t.c.query('.') AS temporary_dependency FROM @object_header.nodes('/object_header/temporary_dependency') AS t(c)
	) x

	OPEN @t_cursor

	FETCH NEXT FROM @t_cursor INTO @t_type, @t_name

	IF @@FETCH_STATUS <> -1
		SET @object_header_text = @object_header_text + @crlf + @crlf + 'TEMPORARY DEPENDENCIES - ' + @s_object_name + ' creates and destroys the following objects:' + @crlf
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		-- ============
		/*<code_comment><![CDATA[capture temporary dependency]]></code_comment>*/
		SET @object_header_text = @object_header_text + @crlf + @tab + @t_type + ' : ' + @t_name
		-- ============
		FETCH NEXT FROM @t_cursor INTO @t_type, @t_name
	END
	/*<code_comment><![CDATA[end cursor: for each temporary dependency]]></code_comment>*/
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare output dependency variables]]></code_comment>*/
	DECLARE	@o_cursor CURSOR
				, @o_change NVARCHAR(128)
				, @o_type NVARCHAR(128)
				, @o_name NVARCHAR(512)
	-- ============
	-- ============
	/*<code_comment><![CDATA[begin cursor: for each output dependency]]></code_comment>*/
	SET @o_cursor = CURSOR FOR
	SELECT output_dependency.value('(/output_dependency/change_type)[1]', 'NVARCHAR(128)')
		, output_dependency.value('(/output_dependency/object_type)[1]', 'NVARCHAR(128)')
		, output_dependency.value('(/output_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)')
	FROM (
		SELECT t.c.query('.') AS output_dependency FROM @object_header.nodes('/object_header/output_dependency') AS t(c)
	) x

	OPEN @o_cursor

	FETCH NEXT FROM @o_cursor INTO @o_change, @o_type, @o_name

	IF @@FETCH_STATUS <> -1
		SET @object_header_text = @object_header_text + @crlf + @crlf + 'OUTPUT DEPENDENCIES - ' + @s_object_name + ' modifies, creates, or destroys on the following objects:' + @crlf
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		-- ============
		/*<code_comment><![CDATA[capture output dependency]]></code_comment>*/
		SET @object_header_text = @object_header_text + @crlf + @tab + @o_change + ' : ' + @o_type + ' : ' + @o_name
		-- ============
		FETCH NEXT FROM @o_cursor INTO @o_change, @o_type, @o_name
	END
	/*<code_comment><![CDATA[end cursor: for each output dependency]]></code_comment>*/
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare referencing dependency variables]]></code_comment>*/
	DECLARE	@r_cursor CURSOR
				, @r_type NVARCHAR(128)
				, @r_name NVARCHAR(512)
	-- ============
	-- ============
	/*<code_comment><![CDATA[begin cursor: for each referencing dependency]]></code_comment>*/
	SET @r_cursor = CURSOR FOR
	SELECT referencing_dependency.value('(/referencing_dependency/object_type)[1]', 'NVARCHAR(128)')
		, referencing_dependency.value('(/referencing_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)')
	FROM (
		SELECT t.c.query('.') AS referencing_dependency FROM @object_header.nodes('/object_header/referencing_dependency') AS t(c)
	) x

	OPEN @r_cursor

	FETCH NEXT FROM @r_cursor INTO @r_type, @r_name

	IF @@FETCH_STATUS <> -1
		SET @object_header_text = @object_header_text + @crlf + @crlf + 'REFERENCING DEPENDENCIES - ' + @s_object_name + ' is referenced by the following objects:' + @crlf
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		-- ============
		/*<code_comment><![CDATA[capture referencing dependency]]></code_comment>*/
		SET @object_header_text = @object_header_text + @crlf + @tab + @r_type + ' : ' + @r_name
		-- ============
		FETCH NEXT FROM @r_cursor INTO @r_type, @r_name
	END
	/*<code_comment><![CDATA[end cursor: for each referencing dependency]]></code_comment>*/
	-- ============

	-- ============
	/*<code_comment><![CDATA[declare object comment variables]]></code_comment>*/
	DECLARE	@c_cursor CURSOR
				, @c_seq INT
				, @c_comment NVARCHAR(MAX)
	-- ============
	-- ============
	/*<code_comment><![CDATA[begin cursor: for each object comment]]></code_comment>*/
	SET @c_cursor = CURSOR FOR
	SELECT object_comment.value('(/object_comment/comment_sequence)[1]', 'INT')
		, object_comment.value('(/object_comment/code_comment)[1]', 'NVARCHAR(MAX)')
	FROM (
		SELECT t.c.query('.') AS object_comment FROM @object_header.nodes('/object_header/object_comment') AS t(c)
	) x
	ORDER BY object_comment.value('(/object_comment/comment_sequence)[1]', 'INT')

	OPEN @c_cursor

	FETCH NEXT FROM @c_cursor INTO @c_seq, @c_comment

	IF @@FETCH_STATUS <> -1
		SET @object_header_text = @object_header_text + @crlf + @crlf + 'CODE COMMENTS' + @crlf
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		-- ============
		/*<code_comment><![CDATA[capture code comment]]></code_comment>*/
		SET @object_header_text = @object_header_text + @crlf + @tab + CAST(@c_seq AS NVARCHAR) + N'. ' + @c_comment
		-- ============
		FETCH NEXT FROM @c_cursor INTO @c_seq, @c_comment
	END
	/*<code_comment><![CDATA[end cursor: for each object comment]]></code_comment>*/
	-- ============

	-- ============
	/*<code_comment><![CDATA[return text]]></code_comment>*/
	RETURN @object_header_text
	-- ============
END
GO
